/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
 * <p>Project: jdbc202202 - PstDemo
 * <p>Powered by webrx On 2022-08-22 09:08:50
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class PstDemo {
    Connection conn;


    @Test
    @DisplayName("PreparedStatement查询分页显示")
    void prepage() {
        try {
            //1统计所有多少条记录 总记录
            PreparedStatement pst1 = conn.prepareStatement("select count(*) from t_book");
            ResultSet rs = pst1.executeQuery();
            rs.next();
            System.out.println(rs.getInt(1));
            System.out.println(rs.getInt("count(*)"));

            //获取表中的记录总数
            int recordcount = rs.getInt(1);
            //设定每页显示多少数据
            int pagesize = 1;
            //设置当前页
            int currpage = 3;

            //计算总页数
            int pagecount = recordcount % pagesize == 0 ? recordcount / pagesize : recordcount / pagesize + 1;
            //判断处理分页的合理性。
            if(currpage<1) currpage=1;
            if(currpage>pagecount) currpage = pagecount;
            System.out.printf("第%d页/共%d页，每页%d条，共%d条%n%n", currpage, pagecount, pagesize, recordcount);

            PreparedStatement pst2 = conn.prepareStatement("select * from t_book limit ?,?");
            pst2.setInt(1, currpage * pagesize - pagesize);
            pst2.setInt(2, pagesize);
            rs = pst2.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getLong("id"));
                System.out.println(rs.getString("name"));
                System.out.println("---------------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    @Test
    @DisplayName("PreparedStatement实现数据查询")
    void pstquery() {
        try {
            var pst = conn.prepareStatement("select pdate,price p,name n,author from t_book order by price desc");
            var rs = pst.executeQuery();
            while (rs.next()) {
                Date d = rs.getDate(1);
                LocalDate d2 = d.toLocalDate();
                String d2s = d2.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                System.out.println(d2s);
                System.out.println(rs.getDouble(2));
                System.out.println(rs.getString("n"));
                System.out.println(rs.getString(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    @DisplayName("PreparedStatement实现数据删除")
    void pstdelete() {
        try {
            String sql = "delete from t_book where author = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, "李四");
            //pst.execute();
            //pst.executeUpdate();
            //pst.executeQuery();
            int rows = pst.executeUpdate();
            System.out.println(rows);
            pstquery();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    @DisplayName("PreparedStatement实现数据修改")
    void pstupdate() {
        try {
            /*
            String sql = "update t_book set price = ? where id = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setDouble(1,35);
            pst.setLong(2,99926658374434817L);
            int rows= pst.executeUpdate();*/
            String sql = "update t_book set pdate = ? where id = ?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setLong(2, 99926658374434817L);
            pst.setDate(1, Date.valueOf(LocalDate.of(2008, 5, 1)));
            int rows = pst.executeUpdate();
            System.out.println(rows);
            pstquery();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    @DisplayName("PreparedStatement实现数据插入")
    void pstinsert() {
        try {
            //01
            //String sql = "insert into t_book set name='《java项目初步开始》',price=80,author='James Gosling'";
            //PreparedStatement pst = conn.prepareStatement(sql);

            //02
            String sql = "insert into t_book set name=?,author=?,price=?,pdate=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, "《web2.0前端案例》");
            pst.setString(2, "张三丰");
            pst.setDouble(3, 36);
            //pst.setDate(4,);3
            //pst.setString(4,"2010-5-25");

            //java.sql.Date  java.time.LocalDate(java1.8 新的日期对象，只有日期没有时间)
            pst.setDate(4, Date.valueOf(LocalDate.of(2006, 5, 15)));
            int rows = pst.executeUpdate();
            System.out.println(rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    void tb() {
        try {
            PreparedStatement pst = conn.prepareStatement("show tables");
            ResultSet rs = pst.executeQuery();
            if (rs.isBeforeFirst()) {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            } else {
                System.out.println("查询没有结果");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    @DisplayName("PreparedStatement 测试")
    void dbs() {
        try {
            PreparedStatement pst = conn.prepareStatement("show databases");
            ResultSet rs = pst.executeQuery();
            if (rs.isBeforeFirst()) {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            } else {
                System.out.println("查询没有结果");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @BeforeEach
    void init() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql:/db", "root", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
